import pymysql
from spyder.utils.programs import get_username

from month01.day12.Student import Student


class StudentManager:
    def __init__(self):
        self.connection = None

    def connect(self):
        if not self.connection:
            self.connection = pymysql.connect(
                host='localhost',
                user='root',
                password='root',
                database='db03',
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
        return self.connection

    def close(self):
        if self.connection:
            try:
                self.connection.close()
            except:
                pass
            finally:
                self.connection = None

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()
        return False

    def insert_student(self, student):
        try:
            with self.connection.cursor() as cursor:
                sql = "INSERT INTO students (name, age) VALUES (%s, %s)"
                cursor.execute(sql, (student.name, student.age))
            self.connection.commit()
        except Exception as e:
            self.connection.rollback()
            raise e

    # 查询所有用户
    def get_all_stus(self):
        with self.connection.cursor() as cursor:
            sql = "SELECT * FROM students"
            cursor.execute(sql)
            result = cursor.fetchall()  # 获取所有记录
            return result

    # 根据ID查询单个用户
    def get_stu_by_id(self,stu_id):
        with self.connection.cursor() as cursor:
            sql = "SELECT * FROM students WHERE id = %s"
            cursor.execute(sql, (stu_id,))
            result = cursor.fetchone()  # 获取单条记录
            return result

    def update_stu(self,stu_id, name=None, email=None, age=None):
        with self.connection.cursor() as cursor:
            updates = []
            params = []

            if name is not None:
                updates.append("name = %s")
                params.append(name)
            if email is not None:
                updates.append("email = %s")
                params.append(email)
            if age is not None:
                updates.append("age = %s")
                params.append(age)

            if updates:
                sql = "UPDATE users SET " + ", ".join(updates) + " WHERE id = %s"
                params.append(stu_id)
                cursor.execute(sql, params)
                self.connection.commit()
                return cursor.rowcount  # 返回受影响的行数
            return 0

    def delete_stu(self,stu_id):
        with self.connection.cursor() as cursor:
            sql = "DELETE FROM student WHERE id = %s"
            cursor.execute(sql, (stu_id,))
        self.connection.commit()
        return cursor.rowcount  # 返回受影响的行数
   
if __name__ == '__main__':
    with StudentManager() as s:
        # stu = Student('聂茹凤', 19)
        # s.insert_student(stu)

        # print(s.get_all_stus())

        print(s.get_stu_by_id(9))